package yams.repository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.StringUtils;
import yams.domain.ContractReportInfo;
import yams.domain.ContractReportRequestObj;
import yams.domain.ReportMonthIncomeInfo;
import yams.domain.ReportRequestObj;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * 报表的数据库服务
 * Created by luffy on 2018/05/14.
 */
@Repository("reportRepository")
public class IReportRepository{

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    PlatformTransactionManager transactionManager;

    public List<ReportMonthIncomeInfo> listMonthIncomeReportData (ReportRequestObj condition){

        Integer pageSize = (Integer)condition.getProperties().get("pageSize");
        Integer pageNumber = (Integer)condition.getProperties().get("pageNumber");

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" select * from ( ");
        strBuilder.append(" select ");
        strBuilder.append(" tb.code bill_code, ");
        strBuilder.append(" tb.name bill_name, ");
        strBuilder.append(" tb.actual_amount bill_amount, ");
        strBuilder.append(" tb.type bill_type, ");
        strBuilder.append(" tu.name customer_name, ");
        strBuilder.append(" tu.phone customer_phone, ");
        strBuilder.append(" tu.id_card customer_idcard, ");

        strBuilder.append(" tb.create_by, ");
        strBuilder.append(" tb.created_date, ");
        strBuilder.append(" tb.status, ");
        strBuilder.append(" tb.payed_amount, ");
        strBuilder.append(" tb.actual_amount, ");
        strBuilder.append(" tb.pay_method, ");

        strBuilder.append(" ta.city, ");
        strBuilder.append(" ta.name apartment_name, ");
        strBuilder.append(" tu2.name manager_name ");

        strBuilder.append(" from t_bill tb ");
        strBuilder.append(" LEFT JOIN t_user tu on tb.user_id = tu.pk_id ");
        strBuilder.append(" LEFT JOIN t_house th ON tb.house_id = th.pk_id ");
        strBuilder.append(" LEFT JOIN t_apartment ta ON th.apart_id = ta.pk_id ");
        strBuilder.append(" LEFT JOIN t_user tu2 on th.manager = tu2.pk_id ");
        strBuilder.append(" ) tt ");

        strBuilder.append(condition2Str(condition));
        strBuilder.append(" order by created_date desc ");
        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
        return transactionTemplate.execute(status -> {
            List<ReportMonthIncomeInfo> resultList = new ArrayList<>();
            List<java.util.Map<String, Object>> rows = jdbcTemplate.queryForList(strBuilder.toString());
            for (java.util.Map row : rows) {
                ReportMonthIncomeInfo obj = new ReportMonthIncomeInfo();
                obj.setBillNo((String) (row.get("bill_code")));
                obj.setBillName((String) (row.get("bill_name")));
                obj.setBillAmount((BigDecimal)(row.get("bill_amount")));
                obj.setBillType((Integer) (row.get("bill_type")));
                obj.setBillDate((Date)row.get("created_date"));
                obj.setCustomerName((String) (row.get("customer_name")));
                obj.setCustomerTel((String) (row.get("customer_phone")));
                obj.setReceiverName((String) (row.get("create_by")));
                obj.setReceiveAmount((BigDecimal) (row.get("actual_amount")));
                obj.setRecieveWay((String) (row.get("pay_method")));
                obj.setCity((String) (row.get("city")));
                obj.setApartmentName((String) (row.get("apartment_name")));
                obj.setManagerName((String) (row.get("manager_name")));
                obj.setRemark((String) (row.get("remark")));

                resultList.add(obj);
            }
            return resultList;
        });

    }

    public List<ReportMonthIncomeInfo> monthIncomeReportData (ReportRequestObj condition){
        //一页的最大数
        Integer pageSize = (Integer)condition.getProperties().get("pageSize");
        //页数
        Integer pageNumber = (Integer)condition.getProperties().get("pageNumber");

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" select * from ( ");
        strBuilder.append(" select ");
        strBuilder.append(" tb.code bill_code, ");
        strBuilder.append(" tb.name bill_name, ");
        strBuilder.append(" tb.actual_amount bill_amount, ");
        strBuilder.append(" tb.type bill_type, ");
        strBuilder.append(" tu.name customer_name, ");
        strBuilder.append(" tu.phone customer_phone, ");
        strBuilder.append(" tu.id_card customer_idcard, ");

        strBuilder.append(" tb.create_by, ");
        strBuilder.append(" tb.created_date, ");
        strBuilder.append(" tb.status, ");
        strBuilder.append(" tb.payed_amount, ");
        strBuilder.append(" tb.actual_amount, ");
        strBuilder.append(" tb.pay_method, ");

        strBuilder.append(" ta.city, ");
        strBuilder.append(" ta.name apartment_name, ");
        strBuilder.append(" tu2.name manager_name ");

        strBuilder.append(" from t_bill tb ");
        strBuilder.append(" LEFT JOIN t_user tu on tb.user_id = tu.pk_id ");
        strBuilder.append(" LEFT JOIN t_house th ON tb.house_id = th.pk_id ");
        strBuilder.append(" LEFT JOIN t_apartment ta ON th.apart_id = ta.pk_id ");
        strBuilder.append(" LEFT JOIN t_user tu2 on th.manager = tu2.pk_id ");
        strBuilder.append(" ) tt ");

        strBuilder.append(condition2Str(condition));
        strBuilder.append(" order by created_date desc ");
        strBuilder.append(" limit "+ pageNumber * pageSize + ","+ pageSize);



        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
        return transactionTemplate.execute(status -> {
            List<ReportMonthIncomeInfo> resultList = new ArrayList<>();
            List<java.util.Map<String, Object>> rows = jdbcTemplate.queryForList(strBuilder.toString());
            for (java.util.Map row : rows) {
                ReportMonthIncomeInfo obj = new ReportMonthIncomeInfo();
                obj.setBillNo((String) (row.get("bill_code")));
                obj.setBillName((String) (row.get("bill_name")));
                obj.setBillAmount((BigDecimal)(row.get("bill_amount")));
                obj.setBillType((Integer) (row.get("bill_type")));
                obj.setBillDate((Date)row.get("created_date"));
                obj.setCustomerName((String) (row.get("customer_name")));
                obj.setCustomerTel((String) (row.get("customer_phone")));
                obj.setReceiverName((String) (row.get("create_by")));
                obj.setReceiveAmount((BigDecimal) (row.get("actual_amount")));
                obj.setRecieveWay((String) (row.get("pay_method")));
                obj.setCity((String) (row.get("city")));
                obj.setApartmentName((String) (row.get("apartment_name")));
                obj.setManagerName((String) (row.get("manager_name")));
                obj.setRemark((String) (row.get("remark")));

                resultList.add(obj);
            }
            return resultList;
        });

    }

    public Long monthIncomeReportTotal (ReportRequestObj condition){

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" select count(1) from ( ");
        strBuilder.append(" select ");
        strBuilder.append(" tb.code bill_code, ");
        strBuilder.append(" tb.name bill_name, ");
        strBuilder.append(" tb.actual_amount bill_amount, ");
        strBuilder.append(" tb.type bill_type, ");
        strBuilder.append(" tu.name customer_name, ");
        strBuilder.append(" tu.phone customer_phone, ");
        strBuilder.append(" tu.id_card customer_idcard, ");

        strBuilder.append(" tb.create_by, ");
        strBuilder.append(" tb.created_date, ");
        strBuilder.append(" tb.status, ");
        strBuilder.append(" tb.payed_amount, ");
        strBuilder.append(" tb.actual_amount, ");
        strBuilder.append(" tb.pay_method, ");

        strBuilder.append(" ta.city, ");
        strBuilder.append(" ta.name apartment_name, ");
        strBuilder.append(" tu2.name manager_name ");

        strBuilder.append(" from t_bill tb ");
        strBuilder.append(" LEFT JOIN t_user tu on tb.user_id = tu.pk_id ");
        strBuilder.append(" LEFT JOIN t_house th ON tb.house_id = th.pk_id ");
        strBuilder.append(" LEFT JOIN t_apartment ta ON th.apart_id = ta.pk_id ");
        strBuilder.append(" LEFT JOIN t_user tu2 on th.manager = tu2.pk_id ");
        strBuilder.append(" ) tt ");

        strBuilder.append(condition2Str(condition));

        TransactionTemplate transactionTemplate=new TransactionTemplate(transactionManager);
        return transactionTemplate.execute(status -> {
            Long cnt = jdbcTemplate.queryForObject(strBuilder.toString(), Long.class);
            return cnt;
        });


    }

    private String condition2Str(ReportRequestObj condition){

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" where 1=1 ");
        Object city = condition.getProperties().get("city");
        if(city != null && !StringUtils.isEmpty(city)){
            strBuilder.append(" and tt.city = '"+city.toString()+"' ");
        }
        Object manager = condition.getProperties().get("manager_like");
        if(manager != null && !StringUtils.isEmpty(manager)){
            strBuilder.append(" and tt.manager_name like '%"+manager.toString()+"%' ");
        }
        Object apartment = condition.getProperties().get("apartment_like");
        if(apartment != null && !StringUtils.isEmpty(apartment)){
            strBuilder.append(" and tt.apartment_name like '%"+apartment.toString()+"%' ");
        }
        Object user = condition.getProperties().get("user_like");
        if(user != null && !StringUtils.isEmpty(user)){
            strBuilder.append(" and (tt.customer_name like '%"+user+"%' or tt.customer_phone like '%"+user+"%' or tt.customer_idcard like '%"+user+"%') ");
        }
        Object startDate = condition.getProperties().get("_startDate");
        if(startDate != null && !StringUtils.isEmpty(startDate)){
            strBuilder.append(" and created_date > '"+startDate+"' ");
        }
        Object endDate = condition.getProperties().get("_endDate");
        if(endDate != null && !StringUtils.isEmpty(endDate)){
            strBuilder.append(" and created_date < '"+endDate+"' ");
        }

        return strBuilder.toString();

    }

    //合同
    public List<ContractReportInfo> listContractReportData (ContractReportRequestObj condition){

        Integer pageSize = (Integer)condition.getProperties().get("pageSize");
        Integer pageNumber = (Integer)condition.getProperties().get("pageNumber");

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" select * from ( ");
        strBuilder.append(" select ");
        strBuilder.append(" ta.city, ");
        strBuilder.append(" ta.name apartment_name, ");
        strBuilder.append(" tu2.name manager_name, ");
        strBuilder.append(" tc.code, ");
        strBuilder.append(" tu.name customer_name, ");
        strBuilder.append(" CONCAT(ta.name,th.house_number) house_number, ");
        strBuilder.append("tei.name    house_type,");

        strBuilder.append(" tu.phone customer_phone, ");
        strBuilder.append(" tu.id_card customer_card, ");
        strBuilder.append(" tc.pay_cycle, ");
        strBuilder.append(" tc.rental, ");
        strBuilder.append(" tb.pledge_amount, ");
        strBuilder.append(" tc.next_pay_date, ");

        strBuilder.append(" tc.start_date, ");
        strBuilder.append(" tc.end_date, ");
        strBuilder.append(" tb.actual_amount, ");

        strBuilder.append(" DATEDIFF(now(),tc.end_date) isdelay,");
        strBuilder.append(" DATEDIFF(now(),tc.next_pay_date) delayday");

        strBuilder.append(" from t_contract tc ");
        strBuilder.append(" LEFT JOIN t_user tu on tc.user_id = tu.pk_id ");
        strBuilder.append(" LEFT JOIN t_house th on  tc.house_id  = th.pk_id ");
        strBuilder.append(" LEFT JOIN t_apartment ta on th.apart_id = ta.pk_id ");
        strBuilder.append(" LEFT JOIN t_user tu2 on th.manager=tu2.pk_id ");
        strBuilder.append("LEFT JOIN t_enum_item  tei on  th.house_group=tei.code");

        strBuilder.append(" LEFT JOIN t_bill tb on tb.cnr_id = tc.pk_id ");
        strBuilder.append(" ) tt ");

        strBuilder.append(condition2Str(condition));
        strBuilder.append(" order by start_date desc ");
        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
        return transactionTemplate.execute(status -> {
            List<ContractReportInfo> resultList = new ArrayList<>();
            List<java.util.Map<String, Object>> rows = jdbcTemplate.queryForList(strBuilder.toString());
            for (java.util.Map row : rows) {
                ContractReportInfo obj = new ContractReportInfo();
                obj.setCityName((String)row.get("city"));
                obj.setApartmentName((String)row.get("apartment_name"));
                obj.setManageName((String)row.get("manager_name"));
                obj.setContractNo((String)row.get("code"));
                obj.setCustomerName((String)row.get("customer_name"));
                obj.setHouseNo((String)row.get("house_number"));
                obj.setHouseType((String)row.get("house_type"));
                obj.setCustomerPhone((String)row.get("customer_phone"));
                obj.setIdCard((String)row.get("customer_card"));
                obj.setPayMethod((Integer) row.get("pay_cycle"));
                obj.setMonthlyRent((Double) row.get("rental"));
                obj.setPledge((BigDecimal)row.get("pledge_amount"));
                obj.setRentalEndDate((String)row.get("next_pay_date"));
                obj.setRentStartDate((Date) row.get("start_date"));
                obj.setRentEndDate((Date)row.get("end_date"));
                obj.setBillAmount((BigDecimal)row.get("actual_amount"));
                obj.setIsDelay((Integer) row.get("isdelay"));
                obj.setDelayDay((Integer)row.get("delayday"));


                resultList.add(obj);
            }
            return resultList;
        });

    }



    public List<ContractReportInfo> contractReportData (ContractReportRequestObj condition){
        Integer pageSize = (Integer)condition.getProperties().get("pageSize");
        Integer pageNumber = (Integer)condition.getProperties().get("pageNumber");

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" select * from ( ");
        strBuilder.append(" select ");
        strBuilder.append(" ta.city, ");
        strBuilder.append(" ta.name apartment_name, ");
        strBuilder.append(" tu2.name manager_name, ");
        strBuilder.append(" tc.code, ");
        strBuilder.append(" tu.name customer_name, ");
        strBuilder.append(" CONCAT(ta.name,th.house_number) house_number, ");
        strBuilder.append("tei.name    house_type,");

        strBuilder.append(" tu.phone customer_phone, ");
        strBuilder.append(" tu.id_card customer_card, ");
        strBuilder.append(" tc.pay_cycle, ");
        strBuilder.append(" tc.rental, ");
        strBuilder.append(" tb.pledge_amount, ");
        strBuilder.append(" tc.next_pay_date, ");

        strBuilder.append(" tc.start_date, ");
        strBuilder.append(" tc.end_date, ");
        strBuilder.append(" tb.actual_amount, ");

        strBuilder.append(" DATEDIFF(now(),tc.end_date) isdelay,");
        strBuilder.append(" DATEDIFF(now(),tc.next_pay_date) delayday");

        strBuilder.append(" from t_contract tc ");
        strBuilder.append(" LEFT JOIN t_user tu on tc.user_id = tu.pk_id ");
        strBuilder.append(" LEFT JOIN t_house th on  tc.house_id  = th.pk_id ");
        strBuilder.append(" LEFT JOIN t_apartment ta on th.apart_id = ta.pk_id ");
        strBuilder.append(" LEFT JOIN t_user tu2 on th.manager=tu2.pk_id ");
        strBuilder.append(" LEFT JOIN t_enum_item  tei on  th.house_group=tei.code");
        strBuilder.append(" LEFT JOIN (SELECT " +
                "                  sum(pledge_amount) pledge_amount, " +
                "                  sum(actual_amount) actual_amount, " +
                "                  max(cnr_id)        cnr_id " +
                "                FROM t_bill " +
                "                GROUP BY cnr_id) tb ON tb.cnr_id = tc.pk_id ");
        strBuilder.append(" ) tt ");

        strBuilder.append(condition2Str(condition));
         strBuilder.append(" order by apartment_name asc,start_date desc ");


        strBuilder.append(" limit "+ pageNumber * pageSize + ","+ pageSize);




        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
        return transactionTemplate.execute(status -> {
            List<ContractReportInfo> resultList = new ArrayList<>();
            List<java.util.Map<String, Object>> rows = jdbcTemplate.queryForList(strBuilder.toString());
            for (java.util.Map row : rows) {
                ContractReportInfo obj = new ContractReportInfo();
                obj.setCityName((String)row.get("city"));
                obj.setApartmentName((String)row.get("apartment_name"));
                obj.setManageName((String)row.get("manager_name"));
                obj.setContractNo((String)row.get("code"));
                obj.setCustomerName((String)row.get("customer_name"));
                obj.setHouseNo((String)row.get("house_number"));
                obj.setHouseType((String)row.get("house_type"));
                obj.setCustomerPhone((String)row.get("customer_phone"));
                obj.setIdCard((String)row.get("customer_card"));
                obj.setPayMethod((Integer) row.get("pay_cycle"));
                obj.setMonthlyRent((Double) row.get("rental"));
                obj.setPledge((BigDecimal)row.get("pledge_amount"));
                obj.setRentalEndDate((String)row.get("next_pay_date"));
                obj.setRentStartDate((Date) row.get("start_date"));
                obj.setRentEndDate((Date)row.get("end_date"));
                obj.setBillAmount((BigDecimal)row.get("actual_amount"));
                obj.setIsDelay((Integer) row.get("isdelay"));
                obj.setDelayDay((Integer) row.get("delayday"));


                resultList.add(obj);
            }
            return resultList;
        });

    }
    public Long contractReportTotal (ContractReportRequestObj condition){

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" select count(1) from ( ");
        strBuilder.append(" select ");
        strBuilder.append(" ta.city, ");
        strBuilder.append(" ta.name apartment_name, ");
        strBuilder.append(" tu2.name manager_name, ");
        strBuilder.append(" tc.code, ");
        strBuilder.append(" tu.name customer_name, ");
        strBuilder.append(" CONCAT(ta.name,th.house_number) house_number, ");
        strBuilder.append("tei.name    house_type,");

        strBuilder.append(" tu.phone customer_phone, ");
        strBuilder.append(" tu.id_card customer_card, ");
        strBuilder.append(" tc.pay_cycle, ");
        strBuilder.append(" tc.rental, ");
        strBuilder.append(" tb.pledge_amount, ");
        strBuilder.append(" tc.next_pay_date, ");

        strBuilder.append(" tc.start_date, ");
        strBuilder.append(" tc.end_date, ");
        strBuilder.append(" tb.actual_amount, ");
        strBuilder.append(" DATEDIFF(now(),tc.end_date) isdelay,");
        strBuilder.append(" DATEDIFF(now(),tc.next_pay_date) delayday");
        strBuilder.append(" from t_contract tc ");
        strBuilder.append(" LEFT JOIN t_user tu on tc.user_id = tu.pk_id ");
        strBuilder.append(" LEFT JOIN t_house th on  tc.house_id  = th.pk_id ");
        strBuilder.append(" LEFT JOIN t_apartment ta on th.apart_id = ta.pk_id ");
        strBuilder.append(" LEFT JOIN t_user tu2 on th.manager=tu2.pk_id ");
        strBuilder.append("LEFT JOIN t_enum_item  tei on  th.house_group=tei.code");
        strBuilder.append(" LEFT JOIN t_bill tb on tb.cnr_id = tc.pk_id ");
        strBuilder.append(" ) tt ");

        strBuilder.append(condition2Str(condition));

        TransactionTemplate transactionTemplate=new TransactionTemplate(transactionManager);
        return transactionTemplate.execute(status -> {
            Long cnt = jdbcTemplate.queryForObject(strBuilder.toString(), Long.class);
            return cnt;
        });


    }

    private String condition2Str(ContractReportRequestObj condition){

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" where 1=1 ");
        Object city = condition.getProperties().get("city");
        if(city != null && !StringUtils.isEmpty(city)){
            strBuilder.append(" and tt.city = '"+city.toString()+"' ");
        }
        Object manager = condition.getProperties().get("manager_like");
        if(manager != null && !StringUtils.isEmpty(manager)){
            strBuilder.append(" and tt.manager_name like '%"+manager.toString()+"%' ");
        }
        Object apartment = condition.getProperties().get("apartment_like");
        if(apartment != null && !StringUtils.isEmpty(apartment)){
            strBuilder.append(" and tt.apartment_name like '%"+apartment.toString()+"%' ");
        }
        Object user = condition.getProperties().get("user_like");
        if(user != null && !StringUtils.isEmpty(user)){
            strBuilder.append(" and (tt.customer_name like '%"+user+"%' or tt.customer_phone like '%"+user+"%' or tt.customer_card like '%"+user+"%') ");

        }
        Object startDate = condition.getProperties().get("_startDate");
        Object endDate = condition.getProperties().get("_endDate");
        if((startDate != null && !StringUtils.isEmpty(startDate))&&(endDate != null && !StringUtils.isEmpty(endDate))){
            strBuilder.append(" and (start_date > '"+startDate+"'and start_date <'" +endDate+"'or");
            strBuilder.append(" end_date > '"+startDate+"'and end_date <'" +endDate+"')");
        }else if(startDate != null && !StringUtils.isEmpty(startDate)){
            strBuilder.append(" and (start_date > '"+startDate+"' or end_date> '" +startDate+"')");
        }else if(endDate != null && !StringUtils.isEmpty(endDate)){
            strBuilder.append(" and start_date < '"+endDate+"'");
        }

        return strBuilder.toString();

    }


    public BigDecimal sumTodayIncome(String startDate, String endDate){

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" select sum(actual_amount) from t_bill ");
        strBuilder.append(" where 1=1 ");
        strBuilder.append(" and status = 1 ");
        strBuilder.append(" and payed_date > '"+startDate+"' ");
        strBuilder.append(" and payed_date < '"+endDate+"' ");


        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
        return transactionTemplate.execute(status -> {
            BigDecimal obj = jdbcTemplate.queryForObject(strBuilder.toString(), BigDecimal.class);
            return obj;
        });

    }




}
